﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Collections;

namespace Mind.Data
{
    /// <summary>
    /// Classe para acesso as conhexões com diferentes servidores de dados.
    /// </summary>
    public class DataAccessManager
    {
        private static DataAccess _Default;
        private Dictionary<String, DataAccess> _DataAccessConnections;
        private const string DEFAULT_CONNECTION_NAME = "DefaultConnectionString";
        private static DataAccessManager _Connections;

        /// <summary>
        /// Busca as conexões configuradas na sessão connectionStrings
        /// do App.Conf
        /// </summary>
        public static DataAccessManager Connections
        {
            get
            {
                if (_Connections == null)
                    _Connections = new DataAccessManager();
                return _Connections;
            }
        }

        /// <summary>
        /// Busca o nome da conexão default configurada no App.Conf na sessão
        /// appSettings com o nome de DefaultConnectionString;
        /// 
        /// A configuração da conexão deve estar na sessão connectionStrings
        /// do App.Conf
        /// </summary>
        public static DataAccess Default
        {
            get
            {
                if (_Default == null)
                    _Default = Connections[DEFAULT_CONNECTION_NAME];

                return _Default;
            }

            set
            {
                _Default = value;
            }


        }


        public static DataAccess CreateDataAccess(string name, DataAccessProvider provider, string connectionString)
        {
            if (Connections._DataAccessConnections.ContainsKey(name))
                throw new InvalidOperationException("Já existe um DataAccess com este nome");

            DataAccess data = new DataAccess(name, provider, connectionString);
            Connections._DataAccessConnections[name] = data;
            return data;

        }

        public static DataAccess CreateDataAccess(string name, DbProviderFactory provider, string connectionString)
        {
            if (Connections._DataAccessConnections.ContainsKey(name))
                throw new InvalidOperationException("Já existe um DataAccess com este nome");

            DataAccess data = new DataAccess(name, provider, connectionString);
            Connections._DataAccessConnections[name] = data;
            return data;

        }

        /// <summary>
        /// Busca as conexões configuradas na sessão connectionStrings
        /// do App.Conf
        /// </summary>
        public DataAccess this[string connectionName]
        {
            get
            {
                return GetConnection(connectionName);
            }
        }

        /// <summary>
        /// Busca as conexões configuradas na sessão connectionStrings
        /// do App.Conf
        /// </summary>
        public DataAccess GetConnection(string connectionName)
        {
            DataAccess result = _DataAccessConnections[connectionName];
            if (result == null)
            {
                result = new DataAccess(connectionName);
                _DataAccessConnections[connectionName] = result;
            }
            return result;
        }


        private DataAccessManager()
        {
            _DataAccessConnections = new Dictionary<String, DataAccess>();
        }


    }

    public enum DataAccessProvider
    {
        SQLite,
        SQLServer
    }

    /// <summary>
    /// Classe para fácil acesso a dados.
    /// </summary>
    public class DataAccess
    {
        private String _providerName;
        private String _connectionString = "";
        private DbProviderFactory _factory;
        private DataAccessTransaction _currentTransaction;
        public string ConnectionName { get; private set; }

        public String ProviderName
        {
            get
            {
                return _providerName;
            }
        }
        public String ConnectionString
        {
            get
            {
                return _connectionString;
            }
        }
        public DataAccessTransaction CurrentTransaction
        {
            get
            {
                return _currentTransaction;
            }
        }
        protected DbProviderFactory Factory
        {
            get
            {
                return _factory;
            }
        }

        internal DataAccess(string connectionName)
        {
            string connectionId = "";
            _factory = GetFactory(connectionName, out _connectionString, out _providerName, out connectionId);
            this.ConnectionName = connectionId;

        }

        internal DataAccess(string name, DataAccessProvider provider, string connectionString)
        {
            this.ConnectionName = name;
            switch (provider)
            {
                case DataAccessProvider.SQLite:
                    _providerName = "System.Data.SQLite";
                    break;
                case DataAccessProvider.SQLServer:
                default:
                    _providerName = "System.Data.SqlClient";
                    break;
            }

            _factory = DbProviderFactories.GetFactory(_providerName);
            _connectionString = connectionString;
        }


        internal DataAccess(string name, DbProviderFactory provider, string connectionString)
        {
            this.ConnectionName = name;
            _providerName = provider.ToString();
            _factory = provider;
            _connectionString = connectionString;
        }

        public DataAccessTransaction CreateTransaction(IsolationLevel isolationLevel)
        {
            DbConnection conn = Factory.CreateConnection();
            conn.ConnectionString = ConnectionString;
            if (_currentTransaction == null || _currentTransaction.TransactionStatus != DataAccessTransaction.Status.Running)
                _currentTransaction = new DataAccessTransaction(conn, isolationLevel);

            return _currentTransaction;
        }

        public DbCommand CreateCommand(string comando, params DbParameter[] parametros)
        {
            DbConnection conn = null;
            DbTransaction trans = null;
            if (_currentTransaction != null && _currentTransaction.TransactionStatus == DataAccessTransaction.Status.Running)
            {
                conn = _currentTransaction.Connection;
                trans = _currentTransaction.GetTransaction();
            }
            else
            {
                conn = Factory.CreateConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
            }

            DbCommand cmd = Factory.CreateCommand();
            cmd.Connection = conn;
            cmd.Transaction = trans;
            cmd.CommandText = SqlSyntaxConverter.CheckSyntax(this, comando);
            cmd.Parameters.AddRange(parametros);

            return cmd;
        }

        public DbParameter CreateParameter()
        {
            return Factory.CreateParameter();
        }


        public DbParameter CreateParameter(string name, object value)
        {
            return CreateParameter(name, value, MapearTipoServidor(value.GetType()));
        }

        public DbParameter CreateParameter(string name, object value, DbType type)
        {
            DbParameter param = this.CreateParameter();
            param.ParameterName = name;
            param.Direction = ParameterDirection.Input;
            param.DbType = type;
            param.Value = value;
            return param;
        }

        private DbProviderFactory GetFactory(string connectionName, out string connectionString, out string providerName, out string connectionId)
        {
            string name = ConfigurationManager.AppSettings[connectionName];
            connectionId = name.Trim();

            if (connectionId.IndexOf('_') > 0)
            {
                connectionId = connectionId.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries)[0];
            }

            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[name];
            connectionString = connectionStringSettings.ConnectionString;
            providerName = connectionStringSettings.ProviderName;
            return DbProviderFactories.GetFactory(providerName);
        }

        /// <summary>
        /// Executa um comando SQL.
        /// </summary>
        /// <param name="comando">O commando SQL</param>
        /// <param name="parametros">Os parametros do comando</param>
        /// <returns>O número de linhas afetadas</returns>
        public int ExecuteNonQuery(string comando, params DbParameter[] parametros)
        {
            int result = 0;

            DbCommand cmd = CreateCommand(comando, parametros);
            result = cmd.ExecuteNonQuery();
            return result;

        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataReader com os objetos retornados pela Query</returns>
        public DbDataReader ExecuteReader(string comando, params DbParameter[] parametros)
        {
            DbDataReader result = null;
            DbCommand cmd = CreateCommand(comando, parametros);
            result = cmd.ExecuteReader();
            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>O objeto da primeira linha e primeira coluna resultante da Query</returns>
        public Object ExecuteScalar(string comando, params DbParameter[] parametros)
        {
            Object result = null;
            DbCommand cmd = CreateCommand(comando, parametros);
            result = cmd.ExecuteScalar();
            return result;
        }

        /// <summary>
        /// Executa uma Query SQL.
        /// </summary>
        /// <param name="comando">A Query SQL</param>
        /// <param name="parametros">Os parametros da Query</param>
        /// <returns>Um DataTable com os resultados da Query</returns>
        public DataTable GetDataTable(string comando, params DbParameter[] parametros)
        {
            DataTable result = new DataTable();
            DbDataAdapter adapter = Factory.CreateDataAdapter();
            DbCommand cmd = CreateCommand(comando, parametros);
            adapter.SelectCommand = cmd;
            adapter.Fill(result);

            return result;
        }

        public DbType MapearTipoServidor(Type tipoOriginal)
        {


            if (tipoOriginal == typeof(String))
                return DbType.String;

            if (tipoOriginal == typeof(Int32))
                return DbType.Int32;

            if (tipoOriginal == typeof(Boolean))
                return DbType.Boolean;

            if (tipoOriginal == typeof(Decimal))
                return DbType.Currency;

            if (tipoOriginal == typeof(DateTime))
                return DbType.DateTime;

            if (tipoOriginal == typeof(Int16))
                return DbType.Int16;

            if (tipoOriginal == typeof(Int64))
                return DbType.Int64;

            if (tipoOriginal == typeof(double))
                return DbType.Double;

            if (tipoOriginal == typeof(byte[]))
                return DbType.Binary;


            //Nullables:

            if (tipoOriginal == typeof(Nullable<Int32>))
                return DbType.Int32;

            if (tipoOriginal == typeof(Nullable<Boolean>))
                return DbType.Boolean;

            if (tipoOriginal == typeof(Nullable<Decimal>))
                return DbType.Currency;

            if (tipoOriginal == typeof(Nullable<DateTime>))
                return DbType.DateTime;

            if (tipoOriginal == typeof(Nullable<Int16>))
                return DbType.Int16;

            if (tipoOriginal == typeof(Nullable<Int64>))
                return DbType.Int64;

            if (tipoOriginal == typeof(Nullable<double>))
                return DbType.Double;


            return DbType.String;
        }

    }


    public class DataAccessTransaction : IDisposable
    {
        public enum Status
        {
            Running,
            RolledBack,
            Commited
        }

        DbConnection _connection;
        DbTransaction _transacao;
        Status _transactionStatus;

        internal DbTransaction GetTransaction()
        {
            return _transacao;
        }

        public Status TransactionStatus
        {
            get
            {
                return _transactionStatus;
            }
        }

        internal DbConnection Connection
        {
            get
            {
                return _connection;
            }
        }

        internal DataAccessTransaction(DbConnection connection, IsolationLevel isolationLevel)
        {
            if (connection.State != ConnectionState.Open)
                connection.Open();

            _connection = connection;
            _transacao = connection.BeginTransaction(isolationLevel);
            _transactionStatus = Status.Running;

        }

        public void Rollback()
        {
            _transacao.Rollback();
            _transactionStatus = Status.RolledBack;

        }

        public void Commit()
        {
            _transacao.Commit();
            _transactionStatus = Status.Commited;

        }



        #region IDisposable Members

        public void Dispose()
        {
            if (_transactionStatus == Status.Running)
                Commit();

            if (_connection.State != ConnectionState.Closed && _connection.State != ConnectionState.Broken)
                _connection.Close();

        }

        #endregion
    }



}


